#! /usr/bin/env python
# -*- coding: utf-8 -*-
# Create by Albert_Chen
# CopyRight (py) 2016年 陈超. All rights reserved by Chao.Chen.
# Create on 2016-10-25

__author__ = 'Albert'

import traceback
import dj_database_url

from mysql.connector.errors import OperationalError
import mysql
import json
from twisted.internet import defer
from scrapy.exceptions import NotConfigured

class MysqlWriter(object):
    """
    A spider that writes to Mysql databases
    """

    @classmethod
    def from_crawler(cls, crawler):

        """Retrieves scrapy crawler and accesses pipeline's settings"""

        # Get MySQL URL from settings
        mysql_url = crawler.settings.get('MYSQL_PIPELINE_URL', None)

        # If doesn't exist, disable the pipeline
        if not mysql_url:
            raise NotConfigured

        # Create the class
        return cls(mysql_url)

    def __init__(self, mysql_url):
        """Opens a MySQL connection pool"""

        # Store the url for future reference
        self.mysql_url = mysql_url
        # Report connection error only once
        self.report_connection_error = True

        # Parse MySQL URL and try to initialize a connection
        conn_kwargs = MysqlWriter.parse_mysql_url(mysql_url)
        self.dbpool = mysql.connector.connect(pool_name='cbi360',
                                              pool_size=3,
                                              charset='utf8',
                                              connection_timeout=5,
                                              **conn_kwargs)
        self.cursor = self.dbpool.cursor()
    def close_spider(self, spider):
        """Discard the database pool on spider close"""
        self.cursor.close()
        self.dbpool.close()

    @defer.inlineCallbacks
    def process_item(self, item, spider):
        """Processes the item. Does insert into MySQL"""

        logger = spider.logger
        logger.info('Insert data to mysql')
        try:
            yield self.do_replace(item)
        except OperationalError:
            if self.report_connection_error:
                logger.error("Can't connect to MySQL: %s" % self.mysql_url)
                self.report_connection_error = False
        except Exception, e:
            logger.error(str(e))
        except:
            print traceback.format_exc()

        # Return the item for the next stage
        defer.returnValue(item)

    def do_replace(self, item):
        """Does the actual REPLACE INTO"""
        sql = """
            REPLACE INTO
                pcauto
                (topic_id, url, datetime, brand, car_type, type, sub_type, title, question, question_img, replay_id, replay, replay_img, replay_to, best_answer)
            VALUES (
                %(topic_id)s,
                %(url)s,
                %(datetime)s,
                %(brand)s,
                %(car_type)s,
                %(type)s,
                %(sub_type)s,
                %(title)s,
                %(question)s,
                %(question_img)s,
                %(replay_id)s,
                %(replay)s,
                %(replay_img)s,
                %(replay_to)s,
                %(best_answer)s
                    )
            """
        args = {}
        for k, v in item.iteritems():

            if isinstance(v, list):
                if k =='best_answer' or "replay_id":
                    args[k] = v[0].replace(',', "")
                else:
                    args[k] = v[0]

            else:
                if k == 'best_answer' or "replay_id":
                    args[k] = v.replace(',', "")
                else:
                    args[k] = v

        try:
            self.cursor.execute(sql, args)
        except Exception,e:
            self.dbpool.rallback()
            raise e
        else:
            self.dbpool.commit()

    @staticmethod
    def parse_mysql_url(mysql_url):
        """
        Parses mysql url and prepares arguments for
        adbapi.ConnectionPool()
        """

        params = dj_database_url.parse(mysql_url)

        conn_kwargs = {}
        conn_kwargs['host'] = params['HOST']
        conn_kwargs['user'] = params['USER']
        conn_kwargs['password'] = params['PASSWORD']
        conn_kwargs['database'] = params['NAME']
        conn_kwargs['port'] = params['PORT']

        # Remove items with empty values
        conn_kwargs = dict((k, v) for k, v in conn_kwargs.iteritems() if v)

        return conn_kwargs


if __name__ == "__main__":
    mysql_url = 'mysql://root:admindev@127.0.0.1:3306/'
    conn_kwargs = MysqlWriter.parse_mysql_url(mysql_url)
    print conn_kwargs